<?php

namespace app\api\controller;

use app\common\controller\Api;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use think\Db;
use think\Exception;
use think\exception\PDOException;

class Salary extends Api
{
    //如果$noNeedLogin为空表示所有接口都需要登录才能请求
    //如果$noNeedRight为空表示所有接口都需要验证权限才能请求
    //如果接口已经设置无需登录,那也就无需鉴权了
    // 无需登录的接口,*表示全部
    protected $noNeedLogin = [''];
    // 无需鉴权的接口,*表示全部
    protected $noNeedRight = ['*'];

    /**
     * 工资单列表
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function list_old()
    {
        $userInfo = $this->auth->getUserinfo();
//        $userInfo['id'] = 82;
//        $userInfo = \app\admin\model\User::get([
//            'id' => $userInfo['id']
//        ]);

        if (!$userInfo) {
            $this->error('您不是教师');
        }

        $time = date('Y');
        if (input('time')) {
            $time = input('time');
        }

        $list = \app\admin\model\Salary::where('salary_month', 'like', $time . '%')
            ->where(['user_id' => $userInfo['id']])
            ->order('salary_month', 'asc')
            ->field('id, salary_month, send_time, total')
            ->select();

        $total = 0;
        if (!empty($list)) {
            foreach ($list as $item => $value) {
//                dump(explode('年', $value->salary_month));die;
                $list[$item]->salary_month = explode('年', $value->salary_month)[1];
                $total += $value->total;
            }
        }

        $this->success('成功', [
            'userinfo' => [
                'avatar' => $userInfo['avatar'],
                'name' => $userInfo['nickname'],
                'subject' => '',
                'teaching_subject' => '',
                'department' => '',
                'total' => $total
            ],
            'salary' => $list
        ]);
    }

    public function list()
    {
        $userInfo = $this->auth->getUserinfo();
//        $userInfo['id'] = 69;
//        $userInfo = \app\admin\model\User::get([
//            'id' => $userInfo['id']
//        ]);

        if (!$userInfo) {
            $this->error('您不是教师');
        }

        $time = date('Y');
        if (input('time')) {
            $time = input('time');
        }

        $list = Db::name('teacher_salary_file')
            ->where('salary_month', 'like', $time . '%')
            ->order('salary_month', 'asc')
            ->field('id, salary_month, send_time, salary_file')
            ->select();

        $total = 0;
        $data = [];
        if (!empty($list)) {
            foreach ($list as $item => $value) {
                $filePath = ROOT_PATH . DS . 'public' . DS . ($value['salary_file'] ?? '');
                $arr[1] = 0;
                if($value['salary_file'] && is_file($filePath))
                {
                    $arr = $this->getSalartInfo($filePath, $userInfo['nickname']);
                    if($arr){
                        $value['salary_month'] = $arr[1] ? explode('年', $value['salary_month'])[1] : '';
                        $value['total'] = $arr[1];
                        $data[] = $value;
						if(is_numeric($arr[1])){
							$total += $arr[1];
						}
                    }
                }
            }
        }

        $this->success('成功', [
            'userinfo' => [
                'avatar' => $userInfo['avatar'],
                'name' => $userInfo['nickname'],
                'subject' => '',
                'teaching_subject' => '',
                'department' => '',
                'total' => $total
            ],
            'salary' => $data
        ]);
    }

    /**
     * 工资详情
     * @throws \think\exception\DbException
     */
    public function info()
    {
        $id = input('id') ?? null;
        if (!$id) {
            $this->error(__('Invalid parameters'));
        }
        $salaryInfo = \app\admin\model\Salary::get($id);
        $userInfo = $this->auth->getUserinfo();
//        $userInfo['id'] = 82;
//        $userInfo = \app\admin\model\User::get([
//            'id' => $userInfo['id']
//        ]);
//        $teacherInfo = \app\admin\model\Teacher::get([
//            'mobile' => $userInfo['mobile']
//        ]);

        $this->success('成功', [
            'info' => $salaryInfo,
            'userInfo' => [
                'avatar' => $userInfo['avatar'],
                'name' => $userInfo['nickname'],
                'subject' => '',
                'teaching_subject' => '',
                'department' => '',
            ]
        ]);
    }

    //     * 工资详情
    public function info_new()
    {
        $userInfo = $this->auth->getUserinfo();
//        $userInfo['id'] = 69;
//        $userInfo = \app\admin\model\User::get([
//            'id' => $userInfo['id']
//        ]);

        if (!$userInfo) {
            $this->error('您不是教师');
        }

        $salaryInfo = [];
        $total = '0.00';
        $id = input('id') ?? null;
        if (!$id) {
            $this->error(__('Invalid parameters'));
        }
        $salary_file = Db::name('teacher_salary_file')
            ->where(['id' => $id])
            ->find();

        //查询到文件
        $filePath = ROOT_PATH . DS . 'public' . DS . ($salary_file['salary_file'] ?? '');
        if($salary_file && is_file($filePath)){
            $arr = $this->getSalartInfo($filePath, $userInfo['nickname']);
            $salaryInfo = $arr[0];
            $total = $arr[1];
        }

        $this->success('成功', [
            'total' => $total,
            'info' => $salaryInfo,
            'userInfo' => [
                'avatar' => $userInfo['avatar'],
                'name' => $userInfo['nickname'],
                'subject' => '',
                'teaching_subject' => '',
                'department' => '',
            ]
        ]);
    }

    protected function getSalartInfo($filePath, $user_nickname)
    {
        //实例化reader
        $ext = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array($ext, ['csv', 'xls', 'xlsx'])) {
            $this->error(__('Unknown data format'));
        }
        if ($ext === 'csv') {
            $file = fopen($filePath, 'r');
            $filePath = tempnam(sys_get_temp_dir(), 'import_csv');
            $fp = fopen($filePath, 'w');
            $n = 0;
            while ($line = fgets($file)) {
                $line = rtrim($line, "\n\r\0");
                $encoding = mb_detect_encoding($line, ['utf-8', 'gbk', 'latin1', 'big5']);
                if ($encoding !== 'utf-8') {
                    $line = mb_convert_encoding($line, 'utf-8', $encoding);
                }
                if ($n == 0 || preg_match('/^".*"$/', $line)) {
                    fwrite($fp, $line . "\n");
                } else {
                    fwrite($fp, '"' . str_replace(['"', ','], ['""', '","'], $line) . "\"\n");
                }
                $n++;
            }
            fclose($file) || fclose($fp);

            $reader = new Csv();
        } elseif ($ext === 'xls') {
            $reader = new Xls();
        } else {
            $reader = new Xlsx();
        }


        //加载文件
        if (!$PHPExcel = $reader->load($filePath)) {
            $this->error(__('未查询到您的工资信息'));
        }
        $currentSheet = $PHPExcel->getSheet(0);  //读取文件中的第一个工作表
        $allColumn = $currentSheet->getHighestDataColumn(); //取得最大的列号
        $allRow = $currentSheet->getHighestRow(); //取得一共有多少行
        $maxColumnNumber = Coordinate::columnIndexFromString($allColumn);

        //循环处理数据
        $referenceRow = [];
        for ( $row = 4; $row <= $allRow; $row++ ){
            for ( $col = 1; $col <= $maxColumnNumber; $col++ ){

                //处理标题
                if($row <= 5) {
                    if (!$PHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row)->isInMergeRange() || $PHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row)->isMergeRangeValueCell()) {
                        // Cell is not merged cell
                        $data[$row][$col] = $PHPExcel->getActiveSheet()->getCellByColumnAndRow($col, $row)->getCalculatedValue();

                        $referenceRow[$col] = $data[$row][$col] ?? '';
                        //This will store the value of cell in $referenceRow so that if the next row is merged then it will use this value for the attribute
                    } else {
                        // Cell is part of a merge-range
                        $data[$row][$col] = $referenceRow[$col] ?? '';
                        //The value stored for this column in $referenceRow in one of the previous iterations is the value of the merged cell
                    }
                }
                //获取当前教师栏
                if($currentSheet->getCellByColumnAndRow(2, $row)->getCalculatedValue() == $user_nickname) {
                    $val = $currentSheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();//PHPEXCEL 识别公式问题的问题解决方案
                    $data[$row][$col] = is_null($val) ? '' : $val;
                }

            }
        }

        //组合数组
        $salaryInfo = [];
        $i = 0;
        $keys = array_keys($data);
        $last_key = end($keys);
        foreach ($data[5] as $key => $child){
            if($key < 3)
                continue;

            $salaryInfo[$i]['child'][] = [
                'name' => $child,
                'value' => $data[$last_key][$key] ?: '0.00',
            ];

            //name
            if(!isset($salaryInfo[$i]['name']) || !$salaryInfo[$i]['name'])
                $salaryInfo[$i]['name'] = '';
            $salaryInfo[$i]['name'] = !$salaryInfo[$i]['name'] ? $data[4][$key] : $salaryInfo[$i]['name'];

            //去掉4、5行合并单元格的 name
            if(isset($salaryInfo[$i]) && count($salaryInfo[$i]['child']) == 1 && ($salaryInfo[$i]['child'][0]['name'] ==  $salaryInfo[$i]['name'])){
                $salaryInfo[$i]['name'] = '';
            }

            if(isset($data[4][$key+1]) && ($data[4][$key+1] || ($data[4][$key+1] == $data[5][$key+1])))//4行下一列有字符  ||  4行下一列和5行下一列相同
            {
                $i++;
            }
        }
        if (!$salaryInfo) {
            $this->error(__('未查询到您的工资信息！'));
        }
        $total = end($salaryInfo)['child'][0]['value'];

        return [$salaryInfo, $total];
    }
}
